package com.jeek.jdbc.myCode.homeWork.preparestatement;

import com.jeek.jdbc.myCode.homeWork.entity.Stus;
import com.jeek.jdbc.myCode.homeWork.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class StudentDao {
    //1. 查询所有学生的数量
    public int countAllStudents() {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null; // 结果集
        int count = 0;
        try {
            String sql = "select count(*) from stus";
            ppst = conn.prepareStatement(sql);
            rs = ppst.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1); // 获取结果集的索引为1的列的值
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(ppst, conn, rs);
        }
        return count;
    }

    //2. 查询所有学生的信息
    public List<Stus> selectAllStudents() {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;
        List<Stus> list = new ArrayList<>();
        try {
            String sql = "select * from stus";
            ppst = conn.prepareStatement(sql);
            rs = ppst.executeQuery();
            while (rs.next()) {
                Stus stus = new Stus(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
                list.add(stus);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(ppst, conn, rs);
        }
        return list;
    }

    //3. 根据学号查询学生的信息
    public Stus selectOneById(String id) {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Stus stus = null;
        try {
            conn = JDBCUtils.getConn();
            String sql = "select * from stus where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1, id);
            rs = ppst.executeQuery();
            while (rs.next()) {
                 stus = new Stus(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
               // return stus;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(ppst, conn, rs);
        }
        return stus;
    }

    //4. 根据姓名查询学生的信息
    public Stus selectOneByName(String name) {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Stus stus = null;
        try {
            conn = JDBCUtils.getConn();
            String sql = "SELECT * FROM stus WHERE name LIKE ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1,  "%" + name + "%");
            rs = ppst.executeQuery();
            while (rs.next()) {
                stus = new Stus(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
                // return stus;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(ppst, conn, rs);
        }
        return stus;
    }


    //5.修改学生的出生日期
    public Boolean updateBirthday(String id, Date birthday) throws SQLException {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Stus stus = null;
        try {
            conn = JDBCUtils.getConn();
            String sql = "update stus set birthday = ? where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1, birthday+"");
            ppst.setString(2, id);
            return ppst.executeUpdate()>0;
        }finally {
            JDBCUtils.close(ppst, conn, rs);
        }
    }


    //6. 删除一个学生的信息
    public boolean deleteStudent(String Id) throws SQLException{
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            String sql = "DELETE FROM stus WHERE id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1, Id);
            return ppst.executeUpdate() > 0; // 影响行数>0表示删除成功
        }
        finally {
            JDBCUtils.close(ppst, conn, rs);
        }
    }

}
